package com.pokermind.dse.util;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.util.Arrays;
import java.util.List;

/**
 * 完整的Schema修复工具
 * 一次性添加所有DAO代码需要的字段
 */
@Component
public class CompleteSchemaFixer {
    
    private final JdbcTemplate jdbcTemplate;
    
    public CompleteSchemaFixer(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    /**
     * 执行完整的Schema修复
     */
    public void fixCompleteSchema() {
        System.out.println("🔧 开始执行完整Schema修复...");
        
        // 所有需要添加的字段（按重要性排序）
        List<AlterCommand> commands = Arrays.asList(
            // 1. 游戏逻辑核心字段
            new AlterCommand("ante", "DECIMAL(10,2) DEFAULT 0", "前注金额"),
            new AlterCommand("next_action_seat", "INT", "下一个行动座位号"),
            new AlterCommand("active_players", "INT", "当前活跃玩家数"),
            new AlterCommand("to_call", "DECIMAL(10,2) DEFAULT 0", "需要跟注的金额"),
            new AlterCommand("min_raise", "DECIMAL(10,2) DEFAULT 0", "最小加注金额"),
            new AlterCommand("status", "VARCHAR(20) DEFAULT 'active'", "手牌状态"),
            
            // 2. 结算字段
            new AlterCommand("final_pot", "DECIMAL(10,2) DEFAULT 0", "最终底池金额"),
            new AlterCommand("rake_amount", "DECIMAL(10,2) DEFAULT 0", "抽水金额"),
            
            // 3. 统计字段
            new AlterCommand("total_actions", "INT DEFAULT 0", "总行动次数"),
            
            // 4. 扩展JSON字段
            new AlterCommand("players_info", "JSON", "玩家信息JSON数据"),
            new AlterCommand("final_results", "JSON", "最终结果JSON数据"),
            
            // 5. 时间统计
            new AlterCommand("duration_seconds", "INT DEFAULT 0", "手牌持续时间(秒)")
        );
        
        int successCount = 0;
        int skipCount = 0;
        int errorCount = 0;
        
        for (AlterCommand command : commands) {
            try {
                if (columnExists(command.columnName)) {
                    System.out.println("  ✓ 字段 " + command.columnName + " 已存在，跳过");
                    skipCount++;
                } else {
                    executeAlterTable(command);
                    System.out.println("  ✅ 成功添加字段: " + command.columnName);
                    successCount++;
                }
            } catch (Exception e) {
                System.err.println("  ❌ 添加字段 " + command.columnName + " 失败: " + e.getMessage());
                errorCount++;
            }
        }
        
        System.out.println("🎯 Schema修复完成!");
        System.out.println("📊 统计: 成功添加 " + successCount + " 个字段，跳过 " + skipCount + " 个字段，失败 " + errorCount + " 个字段");
        
        if (errorCount == 0) {
            System.out.println("✨ 所有字段修复完成，DAO代码应该可以正常工作了！");
        } else {
            System.out.println("⚠️  仍有 " + errorCount + " 个字段修复失败，请检查错误信息");
        }
    }
    
    /**
     * 检查字段是否存在
     */
    private boolean columnExists(String columnName) {
        try {
            String sql = """
                SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
                WHERE TABLE_SCHEMA = 'soulwork' 
                AND TABLE_NAME = 'poker_hands' 
                AND COLUMN_NAME = ?
                """;
            Integer count = jdbcTemplate.queryForObject(sql, Integer.class, columnName);
            return count != null && count > 0;
        } catch (Exception e) {
            return false;
        }
    }
    
    /**
     * 执行ALTER TABLE语句
     */
    private void executeAlterTable(AlterCommand command) {
        String sql = String.format(
            "ALTER TABLE poker_hands ADD COLUMN %s %s COMMENT '%s'", 
            command.columnName, 
            command.dataType, 
            command.comment
        );
        
        System.out.println("  🔨 执行: " + sql);
        jdbcTemplate.execute(sql);
    }
    
    /**
     * 显示修复后的表结构
     */
    public void showTableStructure() {
        System.out.println("\n📋 修复后的 poker_hands 表结构:");
        System.out.println("-".repeat(100));
        
        String sql = """
            SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_COMMENT 
            FROM INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_SCHEMA = 'soulwork' AND TABLE_NAME = 'poker_hands' 
            ORDER BY ORDINAL_POSITION
            """;
            
        try {
            var columns = jdbcTemplate.queryForList(sql);
            System.out.printf("%-25s %-15s %-10s %-20s %s%n", 
                "字段名", "数据类型", "可空", "默认值", "注释");
            System.out.println("-".repeat(100));
            
            for (var column : columns) {
                System.out.printf("%-25s %-15s %-10s %-20s %s%n",
                    column.get("COLUMN_NAME"),
                    column.get("DATA_TYPE"),
                    column.get("IS_NULLABLE"),
                    column.get("COLUMN_DEFAULT"),
                    column.get("COLUMN_COMMENT"));
            }
            
            System.out.println("-".repeat(100));
            System.out.println("📊 总字段数: " + columns.size());
            
        } catch (Exception e) {
            System.err.println("❌ 获取表结构失败: " + e.getMessage());
        }
    }
    
    /**
     * ALTER命令内部类
     */
    private static class AlterCommand {
        final String columnName;
        final String dataType;
        final String comment;
        
        AlterCommand(String columnName, String dataType, String comment) {
            this.columnName = columnName;
            this.dataType = dataType;
            this.comment = comment;
        }
    }
}
